package indi.zjh.dp.db;

import indi.zjh.dp.cache.ObjectCache;
import indi.zjh.dp.error.DatabaseUnwritableException;
import indi.zjh.dp.error.DeleteObjectNotAllowedException;
import indi.zjh.dp.error.NoSuchObjectException;
import indi.zjh.dp.error.ObjectIdInvalidException;
import indi.zjh.dp.error.ParameterNotMatchException;
import indi.zjh.dp.error.UnsuportedFieldException;
import indi.zjh.dp.error.UpdateObjectFailedException;
import indi.zjh.dp.orm.Business;
import indi.zjh.dp.orm.BusinessEvent;
import indi.zjh.dp.orm.BusinessListener;
import indi.zjh.dp.orm.BusinessMapDataStrategy;
import indi.zjh.dp.orm.BusinessProcess;
import indi.zjh.dp.orm.MethodFactory;
import indi.zjh.dp.orm.RelationClass;
import indi.zjh.dp.orm.RelationStrategy;
import indi.zjh.dp.util.RedisUtil;
import indi.zjh.dp.util.StringUtil;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.dom4j.DocumentException;










//import com.mysql.jdbc.DatabaseMetaData;
import com.mysql.jdbc.MysqlDataTruncation;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.exceptions.MySQLTimeoutException;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;
import com.sun.javafx.image.impl.IntArgb;


public class Session implements BusinessListener, AutoCloseable {
	private static Logger logger = Logger.getLogger(Session.class);
		
	protected SessionFactory sFactory;
	protected Connection connection;
	protected Set<Business> objects;
	
	protected Set<String> allTables;
	
	protected boolean isBusy = false; // 连接的状态, 通过SessionFactory的createSession开始占用, 知道session的close释放
	
	protected boolean isNotMng = false; // 该连接是否受SessionFactory管理
	
	// 监听业务模型发生事件的监听器
	protected List<BusinessListener> listener;
	
	// 记录上一条SQL语句执行的时间, 毫秒
	protected long sqlConsumeTime = 0;
	// 记录上一次动作的执行时间, query, insert, execute, alter 方法调用时间, 毫秒
	protected long actConsumeTime = 0;

	public Session() throws Exception
	{
		throw new Exception("Can not create Session by construct method");
	}
	
	public Session(SessionFactory sf) throws SQLException
	{
		this.sFactory = sf;
		this.objects = new HashSet<Business>();
		this.allTables = new HashSet<String>();
		this.listener = new ArrayList<BusinessListener>();
		Configuration conf = this.sFactory.getConfiguration();
		Configuration.MySQLConf mysqlConf = conf.getMySQLConf();
		if(Configuration.isMysql())
		{
			this.connection = DriverManager.getConnection(mysqlConf.url + "?useUnicode=true&characterEncoding=UTF-8&connectTimeout=" + mysqlConf.timeout + "&socketTimeout=" + mysqlConf.timeout, mysqlConf.user, mysqlConf.password);
		}
		else
		{
			this.connection = DriverManager.getConnection(mysqlConf.url);
		}
		this.connection.setAutoCommit(false);
		
		// 初始化时区设置
		try
		{
			if(Configuration.isMysql())
				this.execute("SET time_zone = '+8:00'");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		this.registerBusinessListener(this);
	}
	
	/**
	 * 创建不受管理的会话对象
	 * @param host
	 * @param port
	 * @param username
	 * @param userpwd
	 * @param database
	 * @throws SQLException 
	 */
	public Session(String host, int port, String username, String userpwd, String database) throws SQLException
	{
		//String connectstring = "jdbc:mysql://"+host+":"+port+"/"+database+"?useUnicode=true&characterEncoding=UTF-8&connectTimeout=100000&socketTimeout=100000";
		String connectstring = "jdbc:mysql://"+host+":"+port+"/"+database;
		System.out.println("连接数据库:"+connectstring);
		this.connection = DriverManager.getConnection(connectstring, username, userpwd);
		this.connection.setAutoCommit(true);
		this.isNotMng = true;
		
		// 初始化时区设置
		try
		{
			this.execute("SET time_zone = '+8:00'");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取数据库连接句柄
	 * @return
	 */
	public Connection getConnection()
	{
		return this.connection;
	}
	/**
	 * 判断连接是否被占用来处理业务
	 * @return
	 */
	public boolean isBusy()
	{
		return this.isBusy;
	}
	
	/**
	 * 业务结束, 连接归还连接池
	 * @throws SQLException 
	 */
	public void release() throws SQLException
	{
		this.isBusy = false;
	}
	/**
	 * 占用该连接
	 * @return: 成功返回true, 如果连接已经失效, 则返回false
	 * @throws SQLException
	 */
	public boolean occupy() throws SQLException
	{
		this.isBusy = true;
		if(this.connection.isClosed())
		{
			logger.error("数据库连接已经关闭");
			return false;
		}
		if(!this.connection.isValid(0))
		{
			logger.error("数据库连接已经不可用");
			this.connection.close();
			return false;
		}
		return true;
	}
	
	public SessionFactory getSessionFactory()
	{
		return this.sFactory;
	}
	
	public int loadTables() throws SQLException
	{
		String[] types = {"TABLE"};
		DatabaseMetaData databaseMetaData = this.connection.getMetaData();
		ResultSet tabs = databaseMetaData.getTables(null, null, null, types);
		this.allTables.clear();
		while(tabs.next())
		{
			// 如果是sqlite,忽略sqlite_sequence系统表
			if(!Configuration.isMysql() && tabs.getString("TABLE_NAME").equals("sqlite_sequence"))
				continue;
			this.allTables.add(tabs.getString("TABLE_NAME"));
		}
		return this.allTables.size();
	}
	
	public boolean isTableExist(String tableName)
	{
		return this.allTables.contains(tableName);
	}
	
	/**
	 * 获取当前数据库的所有数据表
	 * @return
	 */
	public String[] getAllTable()
	{
		String[] declare = new String[]{};
		return this.allTables.toArray(declare);
	}
	
	/**
	 * 获取上一条执行SQL的消费时间, 参考这个值, 用来做优化标准
	 * @return
	 */
	public long getLastSqlConsumeTime()
	{
		return this.sqlConsumeTime;
	}
	/**
	 * 获取上一次动作的执行时间, query, insert, execute, alter 方法调用时间, 毫秒
	 * @return
	 */
	public long getLastActConsumeTime()
	{
		return this.actConsumeTime;
	}
	
	
	/**
	 * 查询数据库, 获取结果集
	 * @param sql: sql语句
	 * @param params: sql绑定的参数数组
	 * @return
	 * @throws Exception 
	 */
	public List<Map<String, Object>> query(String sql, Object[] params) throws Exception
	{
		// 记录时间起点
		long stime1 = System.currentTimeMillis();
		long stime2 = 0;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		try
		{
			stmt = this.connection.prepareStatement(sql);
			// 设置查询超时
			if(this.getSessionFactory() != null)
				stmt.setQueryTimeout((int)this.getSessionFactory().getConfiguration().getMySQLConf().timeout/1000);
			// 判断SQL的绑定参数和入参params个数是否一致
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();
			if(count != params.length)
				throw new ParameterNotMatchException();
			
			String[] paramstr = new String[count];
			for(int i = 1; i <= count; ++i)
			{
				stmt.setObject(i, params[i - 1]);
				paramstr[i - 1] = params[i - 1].toString();
			}
			stime2 = System.currentTimeMillis();
			rs = stmt.executeQuery();
			this.sqlConsumeTime = System.currentTimeMillis() - stime2;
			logger.debug("SQL: "+sql+" {"+String.join(", ", paramstr) + "} "+this.getLastSqlConsumeTime()+" 毫秒");
			
			// 获取结果集, 并且组装数据
			ResultSetMetaData rsmd = rs.getMetaData();
			while(rs.next())
			{
				int c = rsmd.getColumnCount();
				Map<String, Object> record = new HashMap<String, Object>();
				for(int i = 1; i <= c; ++i)
				{
					// 读取序列化对象数据
					if(rsmd.getColumnTypeName(i).equalsIgnoreCase("BLOB"))
					{
						Blob inBlob=rs.getBlob(i);                             //获取blob对象  
		                InputStream is=inBlob.getBinaryStream();                //获取二进制流对象  
		                BufferedInputStream bis=new BufferedInputStream(is);    //带缓冲区的流对象  
		                  
		                byte[] buff=new byte[(int) inBlob.length()];  
		                while(-1!=(bis.read(buff, 0, buff.length))){            //一次性全部读到buff中  
		                }
		                
		                ObjectInputStream in=new ObjectInputStream(new ByteArrayInputStream(buff));  
	     
						record.put(rsmd.getColumnName(i), in.readObject());
						
					}
					else
					{
						record.put(rsmd.getColumnName(i), rs.getObject(i));
					}
				}
				result.add(record);
			}
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			if(rs != null)
				rs.close();
			if(stmt != null)
				stmt.close();
		}
		
		// 计算该方法的消耗时间
		this.actConsumeTime = System.currentTimeMillis() - stime1;
		return result;
	}
	
	/**
	 * 查询数据库, 获取结果集
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> query(String sql) throws Exception
	{
		Object[] params = new Object[0];
		return this.query(sql, params);
	}
	
	/**
	 * 更新数据库数据, 包括insert, update, delete
	 * @param sql
	 * @param params
	 * @return: 影响到的数据行数
	 * @throws MysqlDataTruncation: 数据溢出
	 */
	public int execute(String sql, Object[] params) throws MysqlDataTruncation, MySQLSyntaxErrorException, ParameterNotMatchException, DatabaseUnwritableException, MySQLTimeoutException, SQLException
	{
		if(this.getSessionFactory() != null)
		{
			if(!this.getSessionFactory().getConfiguration().isWritable())
				throw new DatabaseUnwritableException();
		}
		long stime1 = System.currentTimeMillis();
		long stime2 = 0;
		PreparedStatement stmt = null;
		int affected = 0;
		try
		{
			stmt = this.connection.prepareStatement(sql);
			if(this.getSessionFactory() != null)
				stmt.setQueryTimeout((int)this.getSessionFactory().getConfiguration().getMySQLConf().timeout/1000);
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();
			if(count != params.length)
			{
				throw new MySQLSyntaxErrorException();
			}
			String[] paramstr = new String[count];
			for(int i = 1; i <= count; ++i)
			{
				stmt.setObject(i, params[i - 1]);
				paramstr[i - 1] = params[i - 1].toString();
			}
			stime2 = System.currentTimeMillis();
			affected = stmt.executeUpdate();
			
			this.sqlConsumeTime = System.currentTimeMillis() - stime2;
			logger.debug("SQL:"+sql+"{"+String.join(", ", paramstr)+"} "+this.getLastSqlConsumeTime()+" 毫秒");
		}
		catch(SQLException e)
		{
			System.out.println("Exception:"+sql);
			throw e;
		}
		finally
		{
			if(stmt != null)
				stmt.close();
		}
		this.actConsumeTime = System.currentTimeMillis() - stime1;
		return affected;
	}
	
	public long[] insert(String sql, Object[] params, boolean isIgnoreDuplicate) throws MysqlDataTruncation, MySQLSyntaxErrorException, MySQLTimeoutException, ParameterNotMatchException, DatabaseUnwritableException, SQLException
	{
		if(!isIgnoreDuplicate)
			return this.insert(sql, params);
	
		long[] ids = null;
		try
		{
			ids = this.insert(sql, params);
		}
		catch(MySQLIntegrityConstraintViolationException ee)
		{
			logger.warn(ee.getMessage());
		}
		catch (SQLException ee) {
			// TODO: handle exception
			if(ee.getMessage().indexOf("constraint violation") == -1)
			{
				throw ee;
			}
			else
			{
				logger.warn(ee.getMessage());
			}
		}
		return ids;
	}
	
	/**
	 * 更新数据库数据, 包括insert, update, delete
	 * @param sql
	 * @param params
	 * @return: 新增的Id数组
	 * @throws MysqlDataTruncation: 数据溢出
	 */
	public long[] insert(String sql, Object[] params) throws MysqlDataTruncation, MySQLSyntaxErrorException, ParameterNotMatchException, DatabaseUnwritableException, MySQLTimeoutException, SQLException, MySQLIntegrityConstraintViolationException
	{
		if(this.getSessionFactory() != null)
		{
			if(!this.getSessionFactory().getConfiguration().isWritable())
				throw new DatabaseUnwritableException();
		}
		long stime1 = System.currentTimeMillis();
		long stime2 = 0;
		PreparedStatement stmt = null;
		int affected = 0;
		long[] retids = null;
		ResultSet rs = null;
		try
		{
			stmt = this.connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			if(this.getSessionFactory() != null)
				stmt.setQueryTimeout((int)this.getSessionFactory().getConfiguration().getMySQLConf().timeout/1000);
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();
			if(count != params.length)
				throw new MySQLSyntaxErrorException();
			
			String[] paramstr = new String[count];
			for(int i = 1; i <= count; ++i)
			{
				if(!Configuration.isMysql() && params[i-1].equals("NULL"))
				{
					stmt.setNull(i, java.sql.Types.INTEGER);
				}
				else
				{
					stmt.setObject(i, params[i - 1]);
				}
				paramstr[i - 1] = params[i - 1].toString();
			}
			
			stime2 = System.currentTimeMillis();
			logger.debug("准备执行SQL:"+sql+" {"+String.join(", ", paramstr)+"}");
			affected = stmt.executeUpdate();
			this.sqlConsumeTime = System.currentTimeMillis() - stime2;
			logger.debug("SQL: "+sql+" {"+String.join(", ", paramstr)+"} "+this.getLastSqlConsumeTime()+" 毫秒");
			
			retids = new long[affected];
			int i = 0;
			// 获取产生的自增Id
			rs = stmt.getGeneratedKeys();
			while(rs.next())
			{
				retids[i++] = rs.getLong(1);
			}
			
		}
		catch(SQLException e)
		{
			throw e;
		}
		finally
		{
			if(rs != null)
				rs.close();
			if(stmt != null)
				stmt.close();
		}
		this.actConsumeTime = System.currentTimeMillis() - stime1;
		
		return retids;
	}
	
	/**
	 * 更新数据库数据
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int execute(String sql) throws MySQLSyntaxErrorException, ParameterNotMatchException, DatabaseUnwritableException, MySQLTimeoutException, SQLException
	{
		return this.execute(sql, new Object[0]);
	}
	
	/**
	 * 修改表结构, 添加索引, 失败的话会抛出异常
	 * @param sql
	 * @param params
	 * @throws MySQLSyntaxErrorException: 操作失败, 比如删除不存在的索引, 添加已存在的索引
	 * @throws ParameterNotMatchException: params绑定参数和sql里的参数不匹配
	 * @throws SQLException: SQL语句错误
	 */
	public void alter(String sql, Object[] params) throws MySQLSyntaxErrorException, ParameterNotMatchException, MySQLTimeoutException, SQLException
	{
		long stime1 = System.currentTimeMillis();
		long stime2 = 0;
		PreparedStatement stmt = null;
		try
		{
			System.out.println(sql);
			stmt = this.connection.prepareStatement(sql);
			stmt.setQueryTimeout((int)this.getSessionFactory().getConfiguration().getMySQLConf().timeout/1000);
			ParameterMetaData pmd = stmt.getParameterMetaData();
			int count = pmd.getParameterCount();
			if(count != params.length)
				throw new ParameterNotMatchException("参数个数不匹配");
			String[] paramstr = new String[count];
			
			for(int i = 1; i <= count; ++i)
			{
				stmt.setObject(i, params[i - 1]);
				paramstr[i - 1] = params[i - 1].toString();
			}
			stime2 = System.currentTimeMillis();
			stmt.execute();
			
			if(!Configuration.isMysql())
			{
				this.connection.commit();
			}
			
			this.sqlConsumeTime = System.currentTimeMillis() - stime2;
			if(paramstr.length == 0)
				logger.debug("SQL: "+sql+" {} "+this.getLastSqlConsumeTime()+" 毫秒");
			else
				logger.debug("SQL: "+sql+" {"+String.join(", ", paramstr+"} "+this.getLastSqlConsumeTime()+" 毫秒"));
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			stmt.close();
		}
		this.actConsumeTime = System.currentTimeMillis() - stime1;
	}
	
	
	/**
	 * 修改表结构, 添加索引, 失败的话会抛出异常
	 * @param sql
	 * @throws MySQLSyntaxErrorException
	 * @throws ParameterNotMatchException
	 * @throws MySQLTimeoutException
	 * @throws SQLException
	 */
	public void alter(String sql) throws MySQLSyntaxErrorException, ParameterNotMatchException, MySQLTimeoutException, SQLException
	{
		this.alter(sql, new Object[0]);
	}
	

	public void commit() throws UnsuportedFieldException, DocumentException, Exception
	{
		long stime1 = System.currentTimeMillis();
		
//		Iterator<Business> iter = this.objects.iterator();
//		while(iter.hasNext())
//		{
//			Business obj = iter.next();
//			//obj.debug();
//			BusinessProcess p = new BusinessProcess();
//			p.type = RelationStrategy.INIT;
//			p.process(this, obj);
//		}

		// 记录全局事务Id
		DataSource.commitTranscation(this);
		
		this.connection.commit();
		this.objects.clear();
		this.actConsumeTime = System.currentTimeMillis() - stime1;
		logger.info("Consume:"+this.actConsumeTime);
	}
	
	
	public void rollback() throws SQLException
	{
		this.connection.rollback();
		this.objects.clear();
	}
	
	/**
	 * 关闭会话
	 * @throws Exception 
	 * @throws DocumentException 
	 */
	public void close() throws DocumentException, Exception
	{
		if(this.isNotMng)
		{
			this.connection.close();
			return;
		}
		
		if(Configuration.getInstance().getMySQLConf().poolSize == 0)
		{
			this.connection.close();
		}
		else
		{
			this.connection.rollback();
			this.release();
		}
	}
	
	/**
	 * 添加业务对象到会话, 等待提交
	 * @param obj
	 * @throws Exception 
	 * @throws DocumentException 
	 * @throws UnsuportedFieldException 
	 */
	public Session save(Business obj) throws UnsuportedFieldException, DocumentException, Exception
	{
		if(obj == null)
			return this;
		this.objects.add(obj);
		
		
//		Iterator<Business> iter = this.objects.iterator();
//		while(iter.hasNext())
//		{
//			Business obj = iter.next();
			//obj.debug();
			BusinessProcess p = new BusinessProcess();
			p.type = RelationStrategy.INIT;
			int opttype = p.process(this, obj);
			
			if(opttype != -1)
			{
				DataSource.add(this, obj, opttype);
			}
//		}
		
		return this;
	}
	
	
	/**
	 * 通过业务模型Id获取业务对象, 会用到缓存
	 * @param cls
	 * @param id
	 * @return
	 * @throws UnsuportedFieldException
	 * @throws DocumentException
	 * @throws Exception
	 */
	public Business getById(Class<?> cls, long id) throws UnsuportedFieldException, DocumentException, Exception
	{
		long stime1 = System.currentTimeMillis();
		
		
		// 检查缓存开关
		RelationClass rc = Configuration.getInstance().getVersion().getRelationClass(cls.getName());
		if(RedisUtil.isEnable())
		{
			Business cacheobj = ObjectCache.getInstance().getObject(rc, id);
			// 缓存命中
			if(cacheobj != null)
			{
				return cacheobj;
			}
		}
		
		
		Business obj = (Business)cls.newInstance();
		BusinessProcess p = new BusinessProcess();
		p.getById(this, cls, id, obj);
		
		MethodFactory.BusinessMarkMethod.invoke(obj);
		
		// 更新缓存
		ObjectCache.getInstance().setObject(rc, obj);
	
		this.actConsumeTime = System.currentTimeMillis() - stime1;
		logger.info("Consume:"+this.actConsumeTime);
		return obj;
	}
	
//	public void get(Business obj) throws DocumentException, Exception
//	{
//		long stime1 = System.currentTimeMillis();
//		long id = obj.getId();
//		if(id <= 0)
//			throw new ObjectIdInvalidException();
//		
//		// 检查缓存开关
//		RelationClass rc = Configuration.getInstance().getVersion().getRelationClass(obj.getClass().getName());
//		if(RedisUtil.isEnable())
//		{
//			Business cacheobj = ObjectCache.getInstance().getObject(rc, obj.getId());
//			// 缓存命中
//			if(cacheobj != null)
//			{
//				System.out.println("before copyproperties "+cacheobj);
//				cacheobj.printIds();
//				obj = cacheobj;
//				//BeanUtils.copyProperties(obj, cacheobj);
//				System.out.println("after copyproperties "+obj);
//				obj.printIds();
//				return;
//			}
//		}
//		
//		BusinessProcess p = new BusinessProcess();
//		p.getById(this, obj.getClass(), id, obj);
//		
//		logger.error("get "+obj);
//		
//		// 更新缓存
//		ObjectCache.getInstance().setObject(rc, obj);
//		
//		this.actConsumeTime = System.currentTimeMillis() - stime1;
//		logger.info("Consume:"+this.actConsumeTime);
//	}
	

	/**
	 * 注册业务模型事件监听者
	 * @param lne
	 */
	public void registerBusinessListener(BusinessListener lne)
	{
		this.listener.add(lne);
	}
	
	public void fireBusinessEvent(BusinessEvent event) throws Exception
	{
		Iterator<BusinessListener> iter = this.listener.iterator();
		while(iter.hasNext())
		{
			iter.next().actionEvent(event);
		}
	}

	@Override
	public void actionEvent(BusinessEvent event) throws Exception{
		// TODO Auto-generated method stub
//		switch(event.getType())
//		{
//		case BusinessEvent.INSERT:
//			event.getRelationClass().processInsert(event);
//			break;
//		case BusinessEvent.DELETE:
//			event.getRelationClass().processDelete(event);
//			break;
//		case BusinessEvent.UPDATE:
//			event.getObject().debug();
//			event.getRelationClass().processUpdate(event);
//			
//			event.getObject().debug();
//			break;
//		case BusinessEvent.SELECT:
//			event.getRelationClass().processSelect(event);
//			break;
//		}
	}
	
}
